package com.hnqcgczy.supplier.dao.impl;

import com.hnqcgczy.supplier.dao.ContactsDao;
import com.hnqcgczy.supplier.db.MyTransaction;
import com.hnqcgczy.supplier.utils.BaseDao;
import com.hnqcgczy.supplier.vo.Contacts;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ContactsDaoImpl extends BaseDao implements ContactsDao {
    public ContactsDaoImpl(MyTransaction myTransaction) {
        super(myTransaction);
    }

    @Override
    public int add(Contacts contacts) throws SQLException {
        //1.准备sql
        String sql = "INSERT INTO `t_contacts`(`contacts_id`, `company_id`, `name`, `position`, `phone`, `telephone`, `email`) VALUES (null, ?, ?, ?, ?, ?, ?)";
        //2.调公共修改方法
        int i = super.update(sql, contacts.getCompanyId(), contacts.getName(), contacts.getPosition(), contacts.getPhone(), contacts.getTelephone(), contacts.getEmail());
        //3.关闭资源
        super.closeResource();
        return i;
    }

    @Override
    public int update(Contacts contacts) throws SQLException {
        //1.准备sql
        String sql = "UPDATE `t_contacts` SET `company_id` = ?, `name` = ?, `position` = ?, `phone` = ?, `telephone` = ?, `email` = ? WHERE `contacts_id` = ?";
        //2.调公共修改方法
        int i = super.update(sql, contacts.getCompanyId(), contacts.getName(), contacts.getPosition(), contacts.getPhone(), contacts.getTelephone(), contacts.getEmail(), contacts.getContactsId());
        //3.关闭资源
        closeResource();
        return i;
    }

    @Override
    public int delete(Integer contactsId) throws SQLException {
        //1.准备sql
        String sql = "DELETE FROM `t_contacts` WHERE `contacts_id` = ?";
        //2.调公共修改方法
        int i = update(sql, contactsId);
        //3.关闭资源
        closeResource();
        return i;
    }

    @Override
    public Contacts findOne(Integer contactsId) throws SQLException {
        //1.准备sql
        String sql = "select contacts_id,company_id,name,position,phone,telephone,email from `t_contacts` where contacts_id =?";
        //2.调公共查询方法
        ResultSet rs = query(sql, contactsId);
        Contacts contact = null;
        if (rs != null) {
            while (rs.next()) {
                contact = new Contacts();
                //将数据库中查出来的内容填充到实体对象
                contact.setContactsId(rs.getInt("contacts_id"));
                contact.setCompanyId(rs.getInt("company_id"));
                contact.setName(rs.getString("name"));
                contact.setPosition(rs.getString("position"));
                contact.setPhone(rs.getString("phone"));
                contact.setTelephone(rs.getString("telephone"));
                contact.setEmail(rs.getString("email"));

            }
        }
        //3.关闭资源
        closeResource();
        return contact;
    }

    @Override
    public List<Contacts> findByLike(Contacts contacts, int start, int size) throws SQLException {
        List<Contacts> lists = null;
        StringBuffer sb = new StringBuffer("select contacts_id,company_id,name,position,phone,telephone,email from `t_contacts` where 1 = 1 ");
        if (contacts != null) {
            //用户名称
            if (contacts.getName() != null && !"".equals(contacts.getName())) {
                sb.append(" and name like '%").append(contacts.getName()).append("%'");
            }

            //手机号码
            if (contacts.getPhone() != null && !"".equals(contacts.getPhone())) {
                sb.append(" and phone like '%").append(contacts.getPhone()).append("%'");
            }

        }
        //分页
        sb.append(" limit ").append(start).append(",").append(size);
        //1.准备sql
        String sql = sb.toString();
        //2.调公共查询方法
        ResultSet rs = query(sql);

        if (rs != null) {
            lists = new ArrayList();
            while (rs.next()) {
                Contacts temp = new Contacts();
                //将数据库中查出来的内容填充到实体对象
                temp.setContactsId(rs.getInt("contacts_id"));
                temp.setCompanyId(rs.getInt("company_id"));
                temp.setName(rs.getString("name"));
                temp.setPosition(rs.getString("position"));
                temp.setPhone(rs.getString("phone"));
                temp.setTelephone(rs.getString("telephone"));
                temp.setEmail(rs.getString("email"));
                lists.add(temp);
                temp = null;
            }
        }
        //3.关闭资源
        closeResource();

        return lists;
    }

    @Override
    public int totals(Contacts contacts) throws SQLException {
        List<Contacts> lists = null;
        StringBuffer sb = new StringBuffer("select count(*) totals from `t_contacts` where 1 = 1 ");
        if (contacts != null) {
            //用户名称
            if (contacts.getName() != null && !"".equals(contacts.getName())) {
                sb.append(" and name like '%").append(contacts.getName()).append("%'");
            }

            //手机号码
            if (contacts.getPhone() != null && !"".equals(contacts.getPhone())) {
                sb.append(" and phone like '%").append(contacts.getPhone()).append("%'");
            }

        }

        //1.准备sql
        String sql = sb.toString();
        //2.调公共查询方法
        ResultSet rs = query(sql);
        int totals = 0;
        if (rs != null) {
            while (rs.next()) {
                totals = rs.getInt("totals");
            }
        }
        //3.关闭资源
        closeResource();
        return totals;
    }
}
